package com.njwb.Util;

import com.njwb.Driver;
import com.njwb.pojo.Bill;
import com.njwb.pojo.Supplier;
import com.njwb.pojo.SupplierBill;
import com.njwb.pojo.Users;
import com.njwb.Driver;

import java.sql.*;
import java.util.ArrayList;

public class UtilDao {
    public String logn(String username, String password, int role) throws SQLException {
        String checkUserSql = "SELECT * FROM users WHERE username = ?";
        String checkUserPassRoleSql = "SELECT * FROM users WHERE username = ? AND password = ? AND role = ?";

        try (Connection conn = Driver.getConnection()) {
            // 1. 检查用户名是否存在
            try (PreparedStatement ps = conn.prepareStatement(checkUserSql)) {
                ps.setString(1, username);
                try (ResultSet rs = ps.executeQuery()) {
                    if (!rs.next()) {
                        return "用户不存在";
                    }
                }
            }

            // 2. 检查用户名+密码+角色是否匹配
            try (PreparedStatement ps = conn.prepareStatement(checkUserPassRoleSql)) {
                ps.setString(1, username);
                ps.setString(2, password);
                ps.setInt(3, role);
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next()) {
                        return "登录成功";
                    } else {
                        return "密码或身份错误";
                    }
                }
            }
        }
    }


    public ArrayList<Supplier> getAllSupplier() throws SQLException{
        String sql = "select * from supplier";


        ArrayList<Supplier> list = new ArrayList<>();
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                list.add(new Supplier(
                        rs.getInt("supplier_id"),
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getString("contact_person"),
                        rs.getString("phone"),
                        rs.getString("address")
                ));
            }
        }
        return list;
    }

    public ArrayList<Supplier> getSupplierByName(String name) throws SQLException {
        String sql = "SELECT * FROM supplier WHERE name = ?";
        ArrayList<Supplier> list = new ArrayList<>();

        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, name); // 精确匹配

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new Supplier(
                            rs.getInt("supplier_id"),
                            rs.getString("name"),
                            rs.getString("description"),
                            rs.getString("contact_person"),
                            rs.getString("phone"),
                            rs.getString("address")
                    ));
                }
            }
        }
        return list;
    }

    public boolean addSupplier(String name, String description, String contact_person, String phone, String address) throws SQLException {
        String sql = "INSERT INTO supplier (name, description, contact_person, phone, address) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setString(2, description);
            ps.setString(3, contact_person);
            ps.setString(4, phone);
            ps.setString(5, address);
            int result = ps.executeUpdate();
            return result > 0;
        }
    }

    public boolean updateSupplier(int supplier_id, String name, String description, String contact_person, String phone, String address) throws SQLException {
        String sql = "UPDATE supplier SET name = ?, description = ?, contact_person = ?, phone = ?, address = ? WHERE supplier_id = ?";
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setString(2, description);
            ps.setString(3, contact_person);
            ps.setString(4, phone);
            ps.setString(5, address);
            ps.setInt(6, supplier_id);
            int result = ps.executeUpdate();
            return result > 0;
        }
    }

    public boolean deleteSupplier(int supplier_id) throws SQLException {
        String checkSql = "SELECT * FROM supplier WHERE supplier_id = ?";
        try (Connection conn = Driver.getConnection();
             PreparedStatement psCheck = conn.prepareStatement(checkSql)) {
            psCheck.setInt(1, supplier_id);
            try (ResultSet rs = psCheck.executeQuery()) {
                if (!rs.next()) {
                    return false; // 不存在该 supplier_id
                }
            }

            // 先删除该供应商的所有账单
            String deleteBillSql = "DELETE FROM bill WHERE supplier_id = ?";
            try (PreparedStatement psDeleteBill = conn.prepareStatement(deleteBillSql)) {
                psDeleteBill.setInt(1, supplier_id);
                psDeleteBill.executeUpdate();
            }

            // 再删除供应商
            String deleteSupplierSql = "DELETE FROM supplier WHERE supplier_id = ?";
            try (PreparedStatement psDeleteSupplier = conn.prepareStatement(deleteSupplierSql)) {
                psDeleteSupplier.setInt(1, supplier_id);
                int result = psDeleteSupplier.executeUpdate();
                return result > 0; // 返回是否成功删除供应商
            }
        }
    }



    public ArrayList<Bill> getAllBill() throws SQLException{
        String sql = "select * from bill join Supplier on bill.supplier_id=Supplier.supplier_id";


        ArrayList<Bill> list = new ArrayList<>();
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                list.add(new Bill(
                        rs.getInt("bill_id"),
                        rs.getString("product_name"),
                        rs.getInt("quantity"),
                        rs.getString("unit"),
                        rs.getDouble("amount"),
                        rs.getBoolean("is_paid"),
                        rs.getString("supplier.name"),
                        rs.getString("product_description"),
                        rs.getDate("bill_time")

                ));
            }
        }
        return list;
    }

    public ArrayList<Bill> getBillByName(String name) throws SQLException {
        String sql = "select * from bill join Supplier on bill.supplier_id=Supplier.supplier_id where product_name=?";


        ArrayList<Bill> list = new ArrayList<>();
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);) {
            ps.setString(1, name); // 精确匹配
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new Bill(
                            rs.getInt("bill_id"),
                            rs.getString("product_name"),
                            rs.getInt("quantity"),
                            rs.getString("unit"),
                            rs.getDouble("amount"),
                            rs.getBoolean("is_paid"),
                            rs.getString("supplier.name"),
                            rs.getString("product_description"),
                            rs.getDate("bill_time")

                    ));
                }
            }
            return list;
        }
    }

    public ArrayList<Bill> getBill(String product_name, Boolean is_paid) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT * FROM bill JOIN supplier ON bill.supplier_id = supplier.supplier_id WHERE 1=1");
        ArrayList<Object> params = new ArrayList<>();

        // 动态拼接条件
        if (product_name != null && !product_name.isEmpty()) {
            sql.append(" AND product_name LIKE ?");
            params.add("%" + product_name + "%"); // 模糊查询
        }
        if (is_paid != null) {
            sql.append(" AND is_paid = ?");
            params.add(is_paid);
        }

        ArrayList<Bill> list = new ArrayList<>();
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql.toString())) {

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new Bill(
                            rs.getInt("bill_id"),
                            rs.getString("product_name"),
                            rs.getInt("quantity"),
                            rs.getString("unit"),
                            rs.getDouble("amount"),
                            rs.getBoolean("is_paid"),
                            rs.getString("name"), // supplier.name
                            rs.getString("product_description"),
                            rs.getDate("bill_time")
                    ));
                }
            }
        }
        return list;
    }


    public boolean addBill(String product_name, int quantity, String unit, Double amount, Boolean is_paid, String supplier_name, String product_description, Date bill_time) throws SQLException {
        try (Connection conn = Driver.getConnection()) {

            String checkSupplierSql = "SELECT supplier_id FROM supplier WHERE name = ?";
            int supplier_id = -1;
            try (PreparedStatement psCheckSupplier = conn.prepareStatement(checkSupplierSql)) {
                psCheckSupplier.setString(1, supplier_name);
                try (ResultSet rs = psCheckSupplier.executeQuery()) {
                    if (rs.next()) {
                        supplier_id = rs.getInt("supplier_id");
                    } else {
                        return false; // 找不到供应商
                    }
                }
            }

            String insertSql = "INSERT INTO bill (product_name, quantity, unit, amount, is_paid, supplier_id, product_description, bill_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            try (PreparedStatement psInsert = conn.prepareStatement(insertSql)) {
                psInsert.setString(1, product_name);
                psInsert.setInt(2, quantity);
                psInsert.setString(3, unit);
                psInsert.setDouble(4, amount);
                psInsert.setBoolean(5, is_paid);
                psInsert.setInt(6, supplier_id);
                psInsert.setString(7, product_description);
                psInsert.setDate(8, new Date(bill_time.getTime()));
                int result = psInsert.executeUpdate();
                return result > 0;
            }
        }
    }
  public boolean deleteBill(int bill_id) throws SQLException{
        String sql="delete from bill where bill_id=? ";
        try(Connection conn = Driver.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql)){
            ps.setInt(1, bill_id);
            int result = ps.executeUpdate();
            return result > 0;
        }

  }
  public boolean modifyBill(int bill_id, String product_name, int quantity, String unit, Double amount, Boolean is_paid, String supplier_name, String product_description, Date bill_time) throws SQLException {
      String checkBillSql = "SELECT * FROM bill WHERE bill_id = ?";
      try (Connection conn = Driver.getConnection();
           PreparedStatement psCheckBill = conn.prepareStatement(checkBillSql)) {
          psCheckBill.setInt(1, bill_id);
          try (ResultSet rs = psCheckBill.executeQuery()) {
              if (!rs.next()) {
                  return false; // bill_id 已存在
              }
          }

          String checkSupplierSql = "SELECT supplier_id FROM supplier WHERE name = ?";
          int supplier_id = -1;
          try (PreparedStatement psCheckSupplier = conn.prepareStatement(checkSupplierSql)) {
              psCheckSupplier.setString(1, supplier_name);
              try (ResultSet rs = psCheckSupplier.executeQuery()) {
                  if (rs.next()) {
                      supplier_id = rs.getInt("supplier_id");
                  } else {
                      return false; // 找不到供应商
                  }
              }
          }

          // 改为 UPDATE 语句
          String updateSql = "UPDATE bill SET product_name = ?, quantity = ?, unit = ?, amount = ?, is_paid = ?, supplier_id = ?, product_description = ?, bill_time = ? WHERE bill_id = ?";
          try (PreparedStatement psUpdate = conn.prepareStatement(updateSql)) {
              psUpdate.setString(1, product_name);
              psUpdate.setInt(2, quantity);
              psUpdate.setString(3, unit);
              psUpdate.setDouble(4, amount);
              psUpdate.setBoolean(5, is_paid);
              psUpdate.setInt(6, supplier_id);
              psUpdate.setString(7, product_description);
              psUpdate.setDate(8, new Date(bill_time.getTime()));
              psUpdate.setInt(9, bill_id);
              int result = psUpdate.executeUpdate();
              return result > 0; // 返回是否成功更新
          }
      }
    }
    public ArrayList<Users> getAllUsers() throws SQLException {
        String sql = "SELECT * FROM users";
        ArrayList<Users> list = new ArrayList<>();

        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                list.add(new Users(
                        rs.getInt("id"),
                        rs.getString("username"),
                        rs.getString("password"),
                        rs.getString("gender"),
                        rs.getInt("age"),
                        rs.getString("phone"),
                        rs.getString("address"),
                        rs.getInt("role")
                ));
            }
        }
        return list;
    }

    public ArrayList<Users> getUsersByUsername(String username) throws SQLException {
        String sql = "SELECT * FROM users WHERE username = ?";
        ArrayList<Users> list = new ArrayList<>();

        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, username); // 精确匹配

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(new Users(
                            rs.getInt("id"),
                            rs.getString("username"),
                            rs.getString("password"),
                            rs.getString("gender"),
                            rs.getInt("age"),
                            rs.getString("phone"),
                            rs.getString("address"),
                            rs.getInt("role")
                    ));
                }
            }
        }
        return list;
    }

    public boolean addUser(String username, String password, String gender, int age, String phone, String address, int role) throws SQLException {
        String checkSql = "SELECT * FROM users WHERE username = ?";
        try (Connection conn = Driver.getConnection();
             PreparedStatement psCheck = conn.prepareStatement(checkSql)) {
            psCheck.setString(1, username);
            try (ResultSet rs = psCheck.executeQuery()) {
                if (rs.next()) {
                    return false; // 用户名已存在
                }
            }

            String insertSql = "INSERT INTO users (username, password, gender, age, phone, address, role) VALUES (?, ?, ?, ?, ?, ?, ?)";
            try (PreparedStatement psInsert = conn.prepareStatement(insertSql)) {
                psInsert.setString(1, username);
                psInsert.setString(2, password);
                psInsert.setString(3, gender);
                psInsert.setInt(4, age);
                psInsert.setString(5, phone);
                psInsert.setString(6, address);
                psInsert.setInt(7, role);
                int result = psInsert.executeUpdate();
                return result > 0;
            }
        }
    }
    public boolean updateUser(int id, String username, String password, String gender, int age, String phone, String address, int role) throws SQLException {
        String checkSql = "SELECT * FROM users WHERE id = ?";
        try (Connection conn = Driver.getConnection();
             PreparedStatement psCheck = conn.prepareStatement(checkSql)) {
            psCheck.setInt(1, id);
            try (ResultSet rs = psCheck.executeQuery()) {
                if (!rs.next()) {
                    return false; // 用户不存在
                }
            }

            String updateSql = "UPDATE users SET username = ?, password = ?, gender = ?, age = ?, phone = ?, address = ?, role = ? WHERE id = ?";
            try (PreparedStatement psUpdate = conn.prepareStatement(updateSql)) {
                psUpdate.setString(1, username);
                psUpdate.setString(2, password);
                psUpdate.setString(3, gender);
                psUpdate.setInt(4, age);
                psUpdate.setString(5, phone);
                psUpdate.setString(6, address);
                psUpdate.setInt(7, role);
                psUpdate.setInt(8, id);
                int result = psUpdate.executeUpdate();
                return result > 0;
            }
        }
    }


    public boolean deleteUser(int id) throws SQLException {
        String checkSql = "SELECT * FROM users WHERE id = ?";
        try (Connection conn = Driver.getConnection();
             PreparedStatement psCheck = conn.prepareStatement(checkSql)) {
            psCheck.setInt(1, id);
            try (ResultSet rs = psCheck.executeQuery()) {
                if (!rs.next()) {
                    return false; // 用户不存在
                }
            }

            String deleteSql = "DELETE FROM users WHERE id = ?";
            try (PreparedStatement psDelete = conn.prepareStatement(deleteSql)) {
                psDelete.setInt(1, id);
                int result = psDelete.executeUpdate();
                return result > 0;
            }
        }
    }

    public ArrayList<SupplierBill> getSupplierStatistics() throws SQLException {
        String sql = "SELECT s.supplier_id, s.name, " +
                "IFNULL(SUM(b.amount),0) AS total_amount, " +
                "COUNT(b.bill_id) AS product_count, " +
                "IFNULL(SUM(b.quantity),0) AS total_quantity " +
                "FROM Supplier s " +
                "LEFT JOIN Bill b ON s.supplier_id = b.supplier_id " +
                "GROUP BY s.supplier_id, s.name";

        ArrayList<SupplierBill> list = new ArrayList<>();

        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                int supplier_id = rs.getInt("supplier_id");
                String name = rs.getString("name");
                double sum = rs.getDouble("total_amount");
                int clas = rs.getInt("product_count"); // 商品种类或订单数
                int count = rs.getInt("total_quantity"); // 商品总量

                list.add(new SupplierBill(supplier_id, name, sum, clas, count));
            }
        }

        return list;
    }


}
